﻿#pragma once

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/odbc/odbc.h>
#include <sqlpp11/sqlite3/sqlite3.h>
#include "sqlpp11orm.h"

#include "OCMain/Environment.h"
#include "OCMain/Log.h"
#include "OCMain/Timer.h"
#include "OCMain/FileUtil.h"
#include "OCMain/Date.h"
#include "OCMain/DataStream.h"
#include "OCServer/ServerUtil.h"
#include "FDBFile.h"
#include "drogon/drogon.h"

using namespace OCPP;

namespace sql = sqlpp::sqlite3;

inline std::tuple< size_t, size_t, size_t> decryptID(size_t id)
{
	size_t x = id >> 32;
	size_t y = (id & 0xFFFFFFFF) >> 8;
	size_t z = id & 0xFF;
	return { z,y,x };
}

//x,y 20bit,z 8bit
//[24,24,8]
inline size_t encryptID(uint32 x, uint32 y, uint32 z)
{
	size_t id = (size_t)x << 32 | (size_t)y << 8 | (size_t)z;
	return id;
}

std::shared_ptr<sql::connection> openDB(String fileName, int flags)
{
	sql::connection_config config;
	config.path_to_database = fileName;
	config.flags = flags;
	config.debug = false;

	try {
		return std::make_shared<sql::connection>(config);
	}
	catch (...) {
	}

	return nullptr;
}

std::shared_ptr<sql::connection> initSaveDB(String fileName)
{
	auto saveDB = openDB(fileName, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);

	String sql1 = "CREATE TABLE IF NOT EXISTS file_info(\
				file_gid INTEGER PRIMARY KEY,\
				file_path TEXT,\
				file_md5 TEXT)";
	String sql2 = "CREATE TABLE IF NOT EXISTS file_data(\
				file_md5 TEXT PRIMARY KEY,\
				file_size INTEGER,\
				file_data BLOB)";
	saveDB->execute(sql1);
	saveDB->execute(sql2);
	//saveDB->execute("CREATE UNIQUE INDEX file_path_index ON file_info(file_path);");

	return saveDB;
}

std::shared_ptr<std::ifstream> openFile(String fileName)
{
	return std::make_shared<std::ifstream>(fileName, std::ios::binary);
}

bool gzip = false;
using SaveRow = struct
{
	String file_path;
	size_t file_size;
	String file_md5;
	size_t b;
};
using SaveRowVec = std::vector<SaveRow>;

auto findPath(sql::connection& db)
{
	std::set<String> path;
	std::set<String> md5;

	Tab_FDB::table_file_info table = {};
	auto result = (db)(select(table.file_path, table.file_md5).from(table).unconditionally());
	for (const auto& row : result)
	{
		path.insert(row.file_path);
		md5.insert(row.file_md5);
	}

	return std::make_tuple<>(path, md5);
}

void TestSqlite2()
{
	OCPP_LOG_INFO << "TestSqlite\n";

	String saveFileName = "E:/World_Hillshade.sqlite";
	auto saveDB = openDB(saveFileName, SQLITE_OPEN_READWRITE);
	sql::connection& db = *saveDB;

	auto [path_set, md5_set] = findPath(db);
	OCPP_LOG_INFO << "path count " << path_set.size() << "\n";
	OCPP_LOG_INFO << "md5 count " << md5_set.size() << "\n";

	String srcFolder = "E:/map_caches/World_Hillshade";

	size_t newFileCount = 0;

	StringVector zVec = FileUtil::findFiles(srcFolder);
	zVec.erase(std::find(zVec.begin(), zVec.end(), "."));
	zVec.erase(std::find(zVec.begin(), zVec.end(), ".."));
	std::sort(zVec.begin(), zVec.end(), [](const String& x, const String& y) {return std::stoi(x) < std::stoi(y); });
	for (auto& z : zVec)
	{
		if (z == "." || z == "..") continue;
		//if (z == "13") break;
		StringVector yVec = FileUtil::findFiles(joinPath(srcFolder,z));
		for (auto& y : yVec)
		{
			if (y == "." || y == "..") continue;
			StringVector xVec = FileUtil::findFiles(joinPath(srcFolder, z, y));
			for (auto& x : xVec)
			{
				if (x == "." || x == "..") continue;
				String path = Stringify() << z << "/" << y << "/" << x;
				String fileName = joinPath(srcFolder, path);
				if (path_set.find(path) != path_set.end())
				{
					FileUtil::deleteFile(fileName);
					OCPP_LOG_INFO << "Remove " << path << "\n";
				}
				else
				{
					if (newFileCount == 1)
					{
						db.start_transaction();
					}
					else if (newFileCount == 10000)
					{
						OCPP_LOG_INFO << "(" << newFileCount << ")commit_transaction\n";
						db.commit_transaction();
						newFileCount = 0;
					}

					SaveRow saveRow;
					std::ifstream infile(fileName, std::ios::binary);
					if (!infile) continue;
					std::streambuf* pbuf = infile.rdbuf();
					size_t filesize =
						static_cast<size_t>(pbuf->pubseekoff(0, std::ifstream::end));
					pbuf->pubseekoff(0, std::ifstream::beg);  // rewind
					std::vector<uint8> file_blob;
					file_blob.resize(filesize);
					pbuf->sgetn((char*)&file_blob[0], filesize);
					saveRow.file_md5 = drogon::utils::getMd5((const char*)&file_blob[0], filesize);
					saveRow.file_size = filesize;
					saveRow.file_path = path;


					Tab_FDB::table_file_info table = {};
					Tab_FDB::table_file_data table_data = {};
					(db)(insert_into(table).set(
						table.file_path = saveRow.file_path,
						table.file_md5 = saveRow.file_md5));

					if (md5_set.find(saveRow.file_md5) == md5_set.end())
					{
						md5_set.insert(saveRow.file_md5);
						(db)(insert_into(table_data).set(
							table_data.file_size = saveRow.file_size,
							table_data.file_md5 = saveRow.file_md5,
							table_data.file_data = file_blob));
					}

					OCPP_LOG_INFO << "New " << "(" << newFileCount << ")" << fileName << "\n";
					newFileCount++;
				}
			}
		}
	}

	if (newFileCount > 0)
	{
		OCPP_LOG_INFO << "(" << newFileCount << ")commit_transaction\n";
		db.commit_transaction();
	}
}


void TestSqlite()
{
	OCPP_LOG_INFO << "TestSqlite\n";

	//gzip = true;
	//String fileName = "F:/fdb/CesT.db";
	//String saveFileName = "G:/CesT.sqlite";
	String fileName = "E:/map_caches/google_s.db";
	String saveFileName = "E:/google_s.sqlite";
	//String saveFileName = StringUtil::replaceAll(fileName, ".db", ".sqlite");
	auto srcDB = openDB(fileName, SQLITE_OPEN_READONLY);
	auto saveDB = initSaveDB(saveFileName);
	
	FDBFile fdb(StringUtil::replaceAll(fileName, ".db", ""));
	fdb.open();

	try {
		size_t totalRecord = 0;

		{
			sql::connection& db = *srcDB;
			Tab_FDB::IDX table = {};
			auto result = (db)(select(count(table.i)).from(table).unconditionally());
			if (result.empty())
			{
				return;
			}
			totalRecord = result.begin()->count;
		}


		std::set<String> existMd5;
		std::set<String> existFastId;

		size_t doneRecord = 0;
		{
			sql::connection& db = *saveDB;
			Tab_FDB::table_file_info table = {};
			auto result = (db)(select(count(table.file_gid)).from(table).unconditionally());
			if (result.empty())
			{
				return;
			}
			doneRecord = result.begin()->count;
		}

		size_t writePerCall = 20000;
		size_t start = Math::Floor(doneRecord / writePerCall);
		size_t n = Math::Floor(totalRecord / writePerCall) + 1;
		for (size_t i = 0; i < n; i++)
		{
			AUTO_TIMER_ELAPSE;

			SaveRowVec vec;
			{
				sql::connection& db = *srcDB;
				Tab_FDB::IDX table = {};
				auto result = (db)(select(all_of(table))
					.from(table).unconditionally().limit(writePerCall)
					.offset(i* writePerCall));
				for (const auto& row : result)
				{
					Json::Value val;
					val[Tab_FDB::Field::i] = (Json::UInt64)row.i.value();
					val[Tab_FDB::Field::k] = row.k.value();
					val[Tab_FDB::Field::b] = (Json::UInt64)row.b.value();
					val[Tab_FDB::Field::e] = (Json::UInt64)row.e.value();
					auto decrypt = decryptID(row.i.value());

					SaveRow saveRow;
					size_t z = 0;
					size_t y = 0;
					size_t x = 0;
					if (gzip)
					{
						z = std::get<0>(decrypt);
						y = std::get<1>(decrypt);
						x = std::get<2>(decrypt);
					}
					else
					{
						//z = std::get<0>(decrypt);
						//y = std::get<2>(decrypt);
						//x = std::get<1>(decrypt);

						//google
						z = std::get<0>(decrypt);
						y = std::get<1>(decrypt);
						x = std::get<2>(decrypt);
					}
					saveRow.b = row.b.value();
					saveRow.file_size = row.e.value();
					String path = str_format("%d/%d", z, y);
					String name = gzip ? str_format("%d.gz", x) : str_format("%d.jpg", x);
					saveRow.file_path = joinPath(path, name);
					vec.emplace_back(saveRow);
				};
			}
#if 1
			{
				sql::connection& db = *saveDB;

				db.start_transaction();

				Tab_FDB::table_file_info table = {};
				Tab_FDB::table_file_data table_data = {};

				for (auto& saveRow : vec)
				{
					if (existFastId.find(saveRow.file_path) != existFastId.end())
					{
						OCPP_LOG_ERROR << "has same file_path.\n";
						continue;
					}

					existFastId.insert(saveRow.file_path);

					auto dataStream = fdb.read(saveRow.b, saveRow.file_size);
					if (!dataStream)
					{
						OCPP_LOG_ERROR << "b(" << saveRow.b << "),e(" << saveRow.file_size << ")" << "bad read.\n";
						continue;
					}

					String gzData;
					if (gzip)
					{
						gzData = drogon::utils::gzipCompress((const char*)dataStream->getPtr(), saveRow.file_size);
						saveRow.file_size = gzData.size();
					}

					std::vector<uint8> file_blob;
					file_blob.resize(saveRow.file_size);
					if (gzip) {
						memcpy(file_blob.data(), gzData.data(), saveRow.file_size);
					}
					else {
						memcpy(file_blob.data(), dataStream->getPtr(), saveRow.file_size);
					}
					saveRow.file_md5 = drogon::utils::getMd5((const char*)file_blob.data(), saveRow.file_size);

					(db)(insert_into(table).set(
						table.file_path = saveRow.file_path,
						table.file_md5 = saveRow.file_md5));

					if(existMd5.find(saveRow.file_md5) == existMd5.end())
					{
						existMd5.insert(saveRow.file_md5);
						(db)(insert_into(table_data).set(
							table_data.file_size = saveRow.file_size,
							table_data.file_md5 = saveRow.file_md5,
							table_data.file_data = file_blob));
					}
				}
				db.commit_transaction();

				float done = (float)i / n * 100;
				OCPP_LOG_INFO << done << "%\n";
			}
#endif
		}
	}
	catch (const std::exception& e) {
		OCPP_LOG_ERROR << "Encountered error: " << e.what() << '\n';
	}
	catch (...) {
		OCPP_LOG_ERROR << "unknown error.\n";
	}
}